﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook
{
    public class UserManager:ConnectionManager
    {
        

        public DataTable GetUser()
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM USERS";

            DataTable dt = new DataTable();

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            return dt;

        }

        public DataTable GetUserByUserID(string sUserID)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM USERS WHERE UserID = @UserID";
            oCommand.Parameters.Add(new SqlParameter("@UserID", sUserID));


            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetUserByEmployeeID(string _EMPLOYEE_ID)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM USERS WHERE EmpID=@EmpID";
            oCommand.Parameters.Add(new SqlParameter("@EmpID", _EMPLOYEE_ID));
             
            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        #region Authorization Access
        //GETTING ACCESS CODE(GROUP POLICY) IN USER
        public string GetAccessCode(string sUserID)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM USERS WHERE UserID = @UserID";
            oCommand.Parameters.Add(new SqlParameter("@UserID", sUserID));


            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0].Rows[0]["AccessCode"].ToString();

        }
        //GETTING THE USER AUTHORIZATION TYPE IN OBJECT
        public string GetAccessType(int _OBJECT_TYPE, string _ACTIVE_USERID)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataRow dr;
            DataTable dt = new DataTable();

            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM USERS WHERE UserID = @UserID";
            oCommand.Parameters.Add(new SqlParameter("@UserID", _ACTIVE_USERID));


            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            string _ACCESS_CODE = ds.Tables[0].Rows[0]["AccessCode"].ToString();

            oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT POL1.ACCESSTYPE FROM OPOL JOIN  POL1 ON OPOL.GROUPCODE = POL1.GROUPCODE WHERE OPOL.GroupCode = @AccessCode AND POL1.OBJTYPE=@objType";
            oCommand.Parameters.Add(new SqlParameter("@objType", _OBJECT_TYPE));
            oCommand.Parameters.Add(new SqlParameter("@AccessCode", _ACCESS_CODE));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                dr = dt.Rows[0];

                return dr["ACCESSTYPE"].ToString();
            }
            else
            {
                return "Full Access";
            }
        }
        #endregion

        #region Add/Updating User
        public int AddUser(UserUnit oUserUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO USERS (UserID, UserPassword, EmpID, UserName, Department, AccessCode, IsMultiWh, DefaultWhseCode) VALUES (@UserID, @UserPassword, @EmpID, @UserName, @Department, @AccessCode, @IsMultiWh, @DefaultWhseCode)";

                oCommand.Parameters.Add(new SqlParameter("@UserID", oUserUnit.UserID));
                oCommand.Parameters.Add(new SqlParameter("@UserPassword", oUserUnit.Password));
                oCommand.Parameters.Add(new SqlParameter("@EmpID", oUserUnit.EmployeeID));
                oCommand.Parameters.Add(new SqlParameter("@UserName", oUserUnit.UserName));
                oCommand.Parameters.Add(new SqlParameter("@Department", oUserUnit.Department));
                oCommand.Parameters.Add(new SqlParameter("@AccessCode", oUserUnit.AccessCode));
                oCommand.Parameters.Add(new SqlParameter("@IsMultiWh", oUserUnit.IsMultiWarehouse));
                oCommand.Parameters.Add(new SqlParameter("@DefaultWhseCode", oUserUnit.DefaultWarehouse));

                oCommand.ExecuteNonQuery();
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int UpdateUser(UserUnit oUserUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE USERS SET UserPassword=@UserPassword, EmpID=@EmpID, UserName=@UserName, Department=@Department, AccessCode=@AccessCode, IsMultiWh=@IsMultiWh, DefaultWhseCode=@DefaultWhseCode WHERE UserID=@UserID";

                oCommand.Parameters.Add(new SqlParameter("@UserID", oUserUnit.UserID));
                oCommand.Parameters.Add(new SqlParameter("@UserPassword", oUserUnit.Password));
                oCommand.Parameters.Add(new SqlParameter("@EmpID", oUserUnit.EmployeeID));
                oCommand.Parameters.Add(new SqlParameter("@UserName", oUserUnit.UserName));
                oCommand.Parameters.Add(new SqlParameter("@Department", oUserUnit.Department));
                oCommand.Parameters.Add(new SqlParameter("@AccessCode", oUserUnit.AccessCode));
                oCommand.Parameters.Add(new SqlParameter("@IsMultiWh", oUserUnit.IsMultiWarehouse));
                oCommand.Parameters.Add(new SqlParameter("@DefaultWhseCode", oUserUnit.DefaultWarehouse));

                oCommand.ExecuteNonQuery();
                return 0;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

        public DataTable BrowseUserByUserID(string sUserID)
        {
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            sUserID = '%' + sUserID + '%';

            oCommand.Connection = this.Connection;
            oCommand.CommandType = CommandType.Text;
            oCommand.CommandText = "SELECT * FROM USERS WHERE UserID LIKE @UserID";
            oCommand.Parameters.Add(new SqlParameter("@UserID", sUserID));


            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public bool IsUserExists(string _USER_ID)
        {
            bool _IS_EXISTS = false;

            DataTable dt = new DataTable();
            SqlCommand _COMMAND = new SqlCommand();
            SqlDataAdapter _ADAPTER = new SqlDataAdapter();

            _COMMAND.Connection = this.Connection;
            _COMMAND.CommandText = "SELECT * FROM USERS WHERE UserId=@UserId";
            _COMMAND.Parameters.Add(new SqlParameter("@UserId", _USER_ID));

            _ADAPTER.SelectCommand = _COMMAND;
            _ADAPTER.Fill(dt);

            int iRowCount = dt.Rows.Count;

            if (iRowCount > 0)
            {
                _IS_EXISTS = true;
            }

            return _IS_EXISTS;
        }

        public bool IsAuthenticated(string sUserName, string sPassword)
        {
            try
            {
                UserUnit oUserUnit = new UserUnit();
                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();
                DataSet ds = new DataSet();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT * FROM USERS WHERE UserID=@UserID AND UserPassword=@Password";


                oCommand.Parameters.Add(new SqlParameter("@UserID", sUserName));
                oCommand.Parameters.Add(new SqlParameter("@Password", sPassword));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                int iCount = ds.Tables[0].Rows.Count;

                if (iCount > 0)
                {
                    //UserID, UserPassword, DefaultCurrency, CompanyCode, CompanyName, Department, AccessCode
                    oUserUnit.UserID = ds.Tables[0].Rows[0]["UserID"].ToString();
                    oUserUnit.UserName = ds.Tables[0].Rows[0]["UserName"].ToString();
                    oUserUnit.Password = ds.Tables[0].Rows[0]["UserPassword"].ToString();
                    oUserUnit.Department = ds.Tables[0].Rows[0]["Department"].ToString();
                    oUserUnit.AccessCode = ds.Tables[0].Rows[0]["AccessCode"].ToString();

                    oUserUnit.IsMultiWarehouse = (bool)ds.Tables[0].Rows[0]["IsMultiWh"];
                    oUserUnit.DefaultWarehouse = ds.Tables[0].Rows[0]["DefaultWhseCode"].ToString();

                    this.ActiveUser = oUserUnit;

                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        public void ChangePassword(string sUserName, string sPassword)
        {
            UserUnit oUserUnit = new UserUnit();
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            DataSet ds = new DataSet();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE USERS SET UserPassword = @Password WHERE UserID=@UserID";

            oCommand.Parameters.Add(new SqlParameter("@UserID", sUserName));
            oCommand.Parameters.Add(new SqlParameter("@Password", sPassword));

            oCommand.ExecuteNonQuery();
        }

        public void LockUser(string sUserID)
        {

            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OUSR (Status) VALUES ('Locked') WHERE UserID=@UserID";

                oCommand.Parameters.Add(new SqlParameter("@UserID", sUserID));
                oCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        public UserUnit ActiveUser { set; get; }
    }
}
